package com.zr.daoImpl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.zr.dao.UserDao;
import com.zr.model.AttendanceTable;
import com.zr.model.Employee;
import com.zr.model.Leave;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class UserDaoImpl implements UserDao{

	@Override
	public User getUserByNumAndPassword(String userNum, String userPsw) {
		// TODO Auto-generated method stub
		User user = new User();
		StringBuffer sql = new StringBuffer();
		sql.append(" select eid,ename,epsw");
		sql.append(" from employee");
		sql.append(" where eid=? and epsw=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, userNum);
			pst.setString(2, userPsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				user.setUsernum(rs.getInt("eid"));
				user.setUsername(rs.getString("ename"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	
	@Override
	public int getEntryCount() {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(id) as number");
		sql.append(" from entry");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	@Override
	public int getEntryCount(int did) {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(id) as number");
		sql.append(" from entry");
		sql.append(" inner join employee");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" where did=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	@Override
	public List<AttendanceTable> getEntryMSG(int page, int rows) {
		// TODO Auto-generated method stub
		List<AttendanceTable> Atts = new ArrayList<AttendanceTable>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select employee.eid,ename,dname,twork,owork");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" INNER JOIN entry");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				AttendanceTable as = new AttendanceTable();
				as.setId(rs.getInt("employee.eid"));
				as.setUserName(rs.getString("ename"));
				as.setUserDpartment(rs.getString("dname"));
				as.setStartTime(rs.getString("twork"));
				as.setEndTime(rs.getString("owork"));
				Atts.add(as);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Atts;
	}

	@Override
	public List<AttendanceTable> getEntryMSG(int did, int page, int rows) {
		List<AttendanceTable> Atts = new ArrayList<AttendanceTable>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select employee.eid,ename,dname,twork,owork");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" INNER JOIN entry");
		sql.append(" on entry.eid=employee.eid");
		sql.append(" where employee.did = ?");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				AttendanceTable as = new AttendanceTable();
				as.setId(rs.getInt("employee.eid"));
				as.setUserName(rs.getString("ename"));
				as.setUserDpartment(rs.getString("dname"));
				as.setStartTime(rs.getString("twork"));
				as.setEndTime(rs.getString("owork"));
				Atts.add(as);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Atts;
	}

	@Override
	public JSONArray getAllDepartment() {
		JSONArray   department =  new JSONArray();
		Connection  con   =  JDBCUtil.getConnection();
		StringBuffer  sql = new StringBuffer("");
		sql.append(" select did,dname");
		sql.append(" from department");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			ResultSet  rs  =  pst.executeQuery();
			while(rs.next()){
				  JSONObject  depart =  new JSONObject();
				  depart.put("id", rs.getInt("did"));
				  depart.put("text", rs.getString("dname"));
				  department.add(depart);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return department;
	}

	
	@Override
	public int getUserCount(int did) {
		int count = 0;
		StringBuffer  sql  = new StringBuffer("");
		sql.append(" select count(eid) as number");
		sql.append(" from employee");
		sql.append(" where did=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt("number");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<User> getUserMSG(int did, int page, int rows) {
		List<User> Users = new ArrayList<User>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select eid,dname,eposition,ename,esex,eidcard,eage,eborn,enation,eaddress,eeducation,eintime,ephone,eremark");
		sql.append(" from employee");
		sql.append(" INNER JOIN department");
		sql.append(" ON employee.did = department.did");
		sql.append(" where employee.did = ?");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User use = new User();
				use.setUsernum(rs.getInt("eid"));
				use.setUsername(rs.getString("ename"));
				use.setUserdepartment(rs.getString("dname"));
				use.setUserpositon(rs.getString("eposition"));
				use.setUsersex(rs.getString("esex"));
				use.setUseridcard(rs.getString("eidcard"));
				use.setUserage(rs.getInt("eage"));
				use.setUserborn(rs.getString("eborn"));
				use.setUsernation(rs.getString("enation"));
				use.setUseraddress(rs.getString("eaddress"));
				use.setUsereducation(rs.getString("eeducation"));
				use.setUserintime(rs.getString("eintime"));
				use.setUserphone(rs.getString("ephone"));
				use.setUserremark(rs.getString("eremark"));
				Users.add(use);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Users;
	}

	@Override
	public int getOverTimeUserCount(String opinion) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(overtime.id) as counts");
		sql.append(" from overtime");
		if(StringUtils.isNotBlank(opinion)){
			sql.append(" overtime.opinion=?");			
		}
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(opinion)){
				ps.setString(1, opinion);				
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				count=rs.getInt("counts");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return count;
	}

	@Override
	public List<Employee> getOverTimeUser(int page, int rows, String opinion) {
		List<Employee> li=new ArrayList<Employee>();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,department.dname,overtime.btime,overtime.id,");
		sql.append(" overtime.date,overtime.etime,overtime.wcontent,overtime.opinion,overtime.retime");
		sql.append(" from employee");
		sql.append(" inner join overtime");
		sql.append(" on employee.eid=overtime.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		if(StringUtils.isNotBlank(opinion)){
			sql.append(" where overtime.opinion=?");
		}
		sql.append(" limit ?,?");
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(opinion)){
				ps.setString(1, opinion);
				ps.setInt(2, (page-1)*rows);
				ps.setInt(3, rows);
			}else{
				ps.setInt(1, (page-1)*rows);
				ps.setInt(2, rows);			
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				Employee le=new Employee();
				le.setEid(rs.getInt("employee.eid"));
				le.setEname(rs.getString("employee.ename"));
				le.setDepartment(rs.getString("department.dname"));
				le.setDate(rs.getString("overtime.date"));
				le.setWcontent(rs.getString("overtime.wcontent"));
				le.setFirst(rs.getString("overtime.btime"));
				le.setLast(rs.getString("overtime.etime"));
				le.setOpinion(rs.getString("overtime.opinion"));
				le.setRetime(rs.getString("overtime.retime"));
				le.setTempid(rs.getInt("overtime.id"));
				li.add(le);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return li;
	}

	@Override
	public Employee getAuditById(int id) {
		Employee le=new Employee();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,department.dname,overtime.btime,overtime.id,");
		sql.append(" overtime.date,overtime.etime,overtime.wcontent,overtime.opinion,overtime.retime,overtime.state");
		sql.append(" from employee");
		sql.append(" inner join overtime");
		sql.append(" on employee.eid=overtime.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		sql.append(" where overtime.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				le.setEid(rs.getInt("employee.eid"));
				le.setEname(rs.getString("employee.ename"));
				le.setDepartment(rs.getString("department.dname"));
				le.setDate(rs.getString("overtime.date"));
				le.setWcontent(rs.getString("overtime.wcontent"));
				le.setFirst(rs.getString("overtime.btime"));
				le.setLast(rs.getString("overtime.etime"));
				le.setOpinion(rs.getString("overtime.opinion"));
				le.setRetime(rs.getString("overtime.retime"));
				le.setTempid(rs.getInt("overtime.id"));
				le.setState(rs.getString("overtime.state"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return le;
	}

	@Override
	public boolean checkResign(int id, String opinion, String retime, String remake) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" update overtime set overtime.opinion=?,overtime.retime=?,overtime.remake=?");
		sql.append(" where overtime.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setString(1, opinion);
			ps.setString(2, retime);
			ps.setString(3, remake);
			ps.setInt(4, id);
			int i=ps.executeUpdate();
			if(i>0){
				StringBuffer sq=new StringBuffer();
				sq.append(" update overtime set overtime.state='已审核'");
				sq.append(" where overtime.id=?");
				Connection conn=JDBCUtil.getConnection();
				PreparedStatement prs=conn.prepareStatement(sq.toString());
				prs.setInt(1, id);
				int j=prs.executeUpdate();
				if(j>0){
					flag=true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}

}
